# coding: utf-8
# __author__ = 'Hoyin'
# __date__ = '2020/06/02'
# __Desc__ = 从数据库中导出数据到excel数据表中

import os
import sys
import cx_Oracle
import pymysql
from Logger import Logger

log = Logger('db.log', level='info').logger


class DB(object):

    def get_data(self, dbtype=0, sql='select now()'):
        try:
            if dbtype == 0:
                log.info('连接测试库')
                conn = pymysql.connect(user='test', password='test', host='192.168.15.107', port=3308)
            elif dbtype == 1:
                log.info('连接PaaS库')
                conn = pymysql.connect(user='query', password='Xdjk@query', host='10.80.16.66')
            elif dbtype == 2:
                log.info('连接收单库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'Xdjk_dba', '10.80.16.50/pospst', mode=cx_Oracle.SYSDBA)
            elif dbtype == 3:
                log.info('连接历史库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'oracle', '10.80.16.37/his', mode=cx_Oracle.SYSDBA)
            elif dbtype == 4:
                log.info('连接互联网库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'EJhvmykOSWkyNlHJ', '10.80.16.25/webpay', mode=cx_Oracle.SYSDBA)
            elif dbtype == 5:
                log.info('连接风控库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'l3jx6mcAFYakMEej', '10.80.16.29/riskctl', mode=cx_Oracle.SYSDBA)
            elif dbtype == 6:
                log.info('连接JYX库')
                os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
                conn = cx_Oracle.connect('sys', 'oracleJYX1', '10.80.16.49/posp1', mode=cx_Oracle.SYSDBA)
            elif dbtype == 7:
                log.info('连接wkkj库')
                conn = pymysql.connect(user='root', password='h41vDb0fkh1g', host='10.80.16.33')
            elif dbtype == 8:
                log.info('连接bi库')
                conn = pymysql.connect(user='root', password='DCH8Tztd', host='10.80.16.61')
            elif dbtype == 9:
                log.info('连接new-paas库')
                conn = pymysql.connect(user='query', password='Xdjk@query', host='10.80.16.74')
            elif dbtype == 10:
                log.info('连接business库')
                conn = pymysql.connect(user='business_data', password='2uAiKqU4', host='10.80.16.62', port=3307)

            cursor = conn.cursor()
            log.info("开始执行sql:" + sql)
            cursor.execute(sql)
        
            data = []
        
            while True:
                try:
                    row = cursor.fetchone()
                except Exception as e:
                    pass
                if row is None:
                    break
                data.append(row)

            # 获取表头
            fields = cursor.description

            if data:
                if len(data) == 0:
                    log.info("sql结果为空:\r\n {}\r\n".format(sql))
                    print("sql结果为空！ {}")
                    return False, False
                return data, fields
            else:
                log.error("sql执行失败:\r\n {}\r\n".format(sql))
                return False, False

            cursor.close()
            conn.close()

        except Exception as e:
            log.error("sql:\r\n{}\r\n执行出错!\r\n {}\r\n".format(sql, e))
            return False, False


if __name__ == "__main__":
    results, fields = DB().get_data(0, "select now()")
    # print(results)
